package com.example.mardan_demo1;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

@SpringBootTest
public class ConfigDatabase {
    //获取数据库配置信息
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driver-class-name}")
    private String driver;
    @Autowired
    private DataSource dataSource;

    @Test
    public void getApplicationProperties() {
        System.out.println("Database URL: " + url);
        System.out.println("Database Username: " + username);
        System.out.println("Database Password: " + password);
        System.out.println("Database Driver: " + driver);
        checkDatabaseExists("mybatis");
        checkAndCreateUserTable();

    }

    public void checkDatabaseExists(String dbName) {
        String query = "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '" + dbName + "'";

        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(query)) {

            if (resultSet.next()) {
                System.out.println("Database " + dbName + " exists.");
            } else {
                System.out.println("Database " + dbName + " does not exist.");
            }

        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Error while checking database existence: " + e.getMessage());
        }
    }
    //检查user表，不存在则创建
    public void checkAndCreateUserTable() {
        try(Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement()) {
            ResultSet tables = connection.getMetaData().getTables(null,null,"user", null);
            if(!tables.next()){
                System.out.println("table user dose not exist, creating table user");
                String createTableSQL = "CREATE TABLE `user` (" +
                        "`id` INT NOT NULL AUTO_INCREMENT COMMENT '主键ID'," +
                        "`name` VARCHAR(255) NOT NULL COMMENT '用户名称'," +
                        "`age` INT NOT NULL COMMENT '用户年龄'," +
                        "`gender` VARCHAR(255) COMMENT '用户性别'," +
                        "`phone` VARCHAR(255) COMMENT '电话号码'," +
                        "`status` VARCHAR(255) COMMENT '用户状态'," +
                        "`password` VARCHAR(255) COMMENT '用户密码'," +
                        "`createdAt` DATETIME COMMENT '创建时间'," +
                        "`updatedAt` DATETIME COMMENT '更新时间'," +
                        "`email` VARCHAR(255) COMMENT '电子邮件'," +
                        "`userAvatar` VARCHAR(255) COMMENT '用户头像'," +
                        "PRIMARY KEY (`id`)" +
                        ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';";
                statement.executeUpdate(createTableSQL);
                System.out.println("table user created.");
                String insertAdminSQL = "INSERT INTO `user` (`name`, `age`, `gender`, `phone`, `status`, `password`, `createdAt`, `updatedAt`, `email`, `userAvatar`) VALUES " +
                        "('Admin', 30, 'Male', '1234567890', 'Active', 'adminpassword', NOW(), NOW(), 'admin@example.com', 'default_avatar.png')";
                statement.execute(insertAdminSQL);
                System.out.println("Admin data inserted successfully.");
            } else {
                System.out.println("table user exists");
            }
        }catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Error while checking database existence: " + e.getMessage());
        }
    }
}
